import sys
import MySQL
import pickle
import datetime

class SubGroupExpense:
    def __init__(self, SgExp_id, EID, PID, Date, Desc, Amount):
        self.SgExp_id = SgExp_id
        self.EID = EID
        self.PID = PID
        self.Date = Date
        self.Desc = Desc
        self.Amount = Amount

def FormatTimeDateToString(DateTimeObj):
    STR = """%s, %s, %s"""%(str(DateTimeObj.year), str(DateTimeObj.month), str(DateTimeObj.day))
    return STR

def FormatStringtoTimeDate(String):
    Numbers = String.split(',')
    return datetime.date(int(Numbers[0]), int(Numbers[1]), int(Numbers[2]))
    

class SubGroupExpenses:
    def __init__(self, SQLConn, dBName = "SubGroupExpenses"):
        self.SQLConn = SQLConn
        self.dBName = dBName
        
        
        
    def AddSubGroupExpense(self, EID, PID, Date, Disc, Amount):
        self.SQLConn.SendCommand("""
         INSERT INTO %s (Eve_id, Per_Owed_Id, Amount, Exp_Date, Exp_Discription)
         VALUES ('%d', '%d', '%f', '%s', '%s')
       """ % (self.dBName, EID, PID, Amount, FormatTimeDateToString(Date), Disc))
        
    def RemoveSubGroupExpense(self, SgExp_id):
        self.SQLConn.SendCommand("""
         DELETE FROM %s where SgExp_id=%d
         """ % (self.dBName, SgExp_id))
        
    def GetAllSubGroupExpenses(self):
        self.SQLConn.SendCommand("""SELECT SgExp_id, Eve_id, Per_Owed_Id, Amount, Exp_Date, Exp_Discription FROM %s"""%self.dBName)
        rows = self.SQLConn.FetchAll ()
        Expenses = []
        for Exp in rows:
            SgExp_id, EID, PID, Date, Desc, Amount
            Expenses.append(SubGroupExpense(int(Exp[0]), int(Exp[1]), int(Exp[2]), FormatStringtoTimeDate(Exp[4]), Exp[5], float(Exp[3])))
        return Expenses
    
    def GetAllSubGroupExpensesDict(self):
        self.SQLConn.SendCommand("""SELECT SgExp_id, Eve_id, Per_Owed_Id, Amount, Exp_Date, Exp_Discription FROM %s"""%self.dBName)
        rows = self.SQLConn.FetchAll ()
        Expenses = {}
        for Exp in rows:
           Expenses[int(Exp[0])] = SubGroupExpense(int(Exp[0]), int(Exp[1]), int(Exp[2]), FormatStringtoTimeDate(Exp[4]), Exp[5], float(Exp[3]))
        return Expenses
    
    def UpdateSubGroupExpense(self, Expense):
        self.SQLConn.SendCommand("""UPDATE %s
                                SET Eve_id = '%d',
                                Per_Owed_Id = '%d',
                                Amount = '%f',
                                Exp_Date = '%s',
                                Exp_Discription= '%s'
                                WHERE SgExp_id = %d
                                """ % (self.dBName, Expense.EID, Expense.PID, Expense.Amount, FormatTimeDateToString(Expense.Date), Expense.Desc, Expense.SgExp_id))
       
    def GetSubGroupExpenses(self, SgExp_id = -1, EID=-1, PID = -1, Exp_Discription = '', Exp_Date = ''):
        First = True
        str = """WHERE """
        if SgExp_id != -1:
            if First:
                First = False
                str = str + """SgExp_id = '%d' """ % SgExp_id
            else:
                str = str + """AND SgExp_id = '%d' """ % SgExp_id
        if PID != -1:
            if First:
                First = False
                str = str + """Per_Owed_Id = %d """ % PID
            else:
                str = str + """AND Per_Owed_Id = '%d' """ % PID
        if EID != -1:
            if First:
                First = False
                str = str + """Eve_id = %d """ % EID
            else:
                str = str + """AND Eve_id = '%d' """ % EID
        if Exp_Discription != '':
            if First:
                First = False
                str = str + """Exp_Discription = %s """ % Exp_Discription
            else:
                str = str + """AND Exp_Discription = '%s' """ % Exp_Discription
        if Exp_Date != '':
            if First:
                First = False
                str = str + """Exp_Date = %s """ % FormatTimeDateToString(Exp_Date)
            else:
                str = str + """AND Exp_Date = '%s' """ % FormatTimeDateToString(Exp_Date)
                
        
        
        self.SQLConn.SendCommand("""SELECT SgExp_id, Eve_id, Per_Owed_Id, Exp_Date, Exp_Discription, Amount FROM %s """%self.dBName + str)
        Data = self.SQLConn.FetchAll ()
        Res = []
        if len(Data) >  1:
            
            for Exp in Data:
                Res.append(SubGroupExpense(int(Exp[0]), int(Exp[1]), int(Exp[2]), FormatStringtoTimeDate(Exp[3]), Exp[4], float(Exp[5])))
            
        elif len(Data) == 1:
            Exp = Data[0]
            Res = [SubGroupExpense(int(Exp[0]), int(Exp[1]), int(Exp[2]), FormatStringtoTimeDate(Exp[3]), Exp[4], float(Exp[5]))]
        
            
        return Res
    

    def GetTotalSpentByPersonOnSubGroups(self,PID,EID):
        Exps = self.GetSubGroupExpenses(PID = PID, EID = EID)
        Amount = 0.0
        if Exps != None:
            for Exp in Exps:
                Amount = Amount + Exp.Amount
        #else:
            #Amount = Exp.Amount
        return Amount
    

        
        
            
    
    
if __name__ == '__main__':
    SQL = MySQL.Connection()
    
    SQL.SendCommand ("DROP TABLE IF EXISTS SubGroupExpenses")
    SQL.SendCommand("""CREATE TABLE SubGroupExpenses(
                    SgExp_id int unsigned not null auto_increment primary key,
                    Eve_id int,
                    Per_Owed_Id int,
                    Amount float,
                    Exp_Date BLOB,
                    Exp_Discription varchar(1000)
                    )
                    """)

    
    
  
    
        